package com.student.manager.dao.impl;

import com.student.manager.bean.Student;
import com.student.manager.dao.StudentDao;
import com.student.manager.util.JDBCUtil;
import com.student.manager.util.StringUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;


import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class StudentDaoImpl implements StudentDao {

    @Override
    public List<Student> findStudentByPage(int currentPage) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSouce());

        List<Student> students = runner.query("select * from stu limit ? offset ?", new BeanListHandler<Student>(Student.class), PAGE_SIZE, (currentPage - 1) * PAGE_SIZE);

        return students;
    }

    @Override
    public List<Student> findAllStudents() throws SQLException {

        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSouce());
        List<Student> list = runner.query("select * from stu", new BeanListHandler<Student>(Student.class));
        return list;
    }

    @Override
    public Student findStudentById(int sid) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSouce());
        Student student = runner.query("select * from stu where sid = ?", new BeanHandler<Student>(Student.class), sid);
        return student;
    }

    @Override
    public List<Student> likeSearchStudent(String sname, String sgender) throws SQLException {

        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSouce());
        String sql = "select * from stu where 1 = 1";
        List<String> list = new ArrayList<String>();
        if (!StringUtil.isEmpty(sql)) {
            sql = sql + " and sname like ?";
            list.add("%"+sname+"%");
        }

        if (!StringUtil.isEmpty(sgender)) {
            sql = sql + " and gender = ?";
            list.add(sgender);
        }

        List<Student>  students = runner.query(sql, new BeanListHandler<Student>(Student.class), list.toArray());
        return students;
    }

    @Override
    public void insert(Student student) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSouce());

        runner.update("insert into stu values(null, ?,?,?,?,?,?)",
                student.getSname(),
                student.getGender(),
                student.getPhone(),
                student.getBirthday(),
                student.getHobby(),
                student.getInfo());
    }

    @Override
    public void delete(int sid) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSouce());
        runner.update("delete from stu where sid = ?",sid);
    }

    @Override
    public void update(Student student) throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSouce());

        runner.update("update stu set sname = ?, gender = ?,phone = ?, birthday=?, hobby=?, info = ? where sid = ?",
                student.getSname(),
                student.getGender(),
                student.getPhone(),
                student.getBirthday(),
                student.getHobby(),
                student.getInfo(),
                student.getSid());
    }

    @Override
    public int findCount() throws SQLException {
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSouce());

        Long result =  (Long) runner.query("select count(*) from stu", new ScalarHandler());
        return result.intValue();
    }
}
